package com.gongj;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;


import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

  private static final String PATH = "D:\\gongj\\excel-demo\\poi-excel\\";


  @Test
  public void testWrite03() throws IOException {
      // 创建工作簿
      Workbook workbook = new HSSFWorkbook();
      // 创建工作表
      Sheet sheet = workbook.createSheet("03版本的excel");
      // 创建行
      Row row = sheet.createRow(0);
      // 创建列
      Cell cell11 = row.createCell(0);
      cell11.setCellValue("编号");
      Cell cell12 = row.createCell(1);
      cell12.setCellValue("姓名");

      Row row1 = sheet.createRow(1);
      Cell cell21 = row1.createCell(0);
      cell21.setCellValue(1);
      Cell cell22 = row1.createCell(1);
      cell22.setCellValue("胡萝卜");

      //输出文件流
      FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03.xls");
      // 把相应的Excel工作簿存盘
      workbook.write(fileOutputStream);
      //关闭流
      fileOutputStream.close();
      System.out.println("文件生成成功");
  }

    @Test
    public void testWrite07() throws IOException {
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("07版本的excel");
        // 创建行
        Row row = sheet.createRow(0);
        // 创建列
        Cell cell11 = row.createCell(0);
        cell11.setCellValue("编号");
        Cell cell12 = row.createCell(1);
        cell12.setCellValue("姓名");

        Row row1 = sheet.createRow(1);
        Cell cell21 = row1.createCell(0);
        cell21.setCellValue(999);
        Cell cell22 = row1.createCell(1);
        cell22.setCellValue("胡萝卜999");

        //输出文件流
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07.xlsx");
        // 把相应的Excel工作簿存盘
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("文件生成成功");
    }

    @Test
    public void testWrite03BigData() throws IOException {

        long begin = System.currentTimeMillis();
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("03版本的大数据写入");

        //xls文件最大支持65536行
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 5; cellNum++) {
                // 创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("开始写入");
        FileOutputStream out = new FileOutputStream(PATH+"bigdata03.xls");
        workbook.write(out);
        // 关闭流
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }


    @Test
    public void testWrite07BigData() throws IOException {

        long begin = System.currentTimeMillis();
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("07版本的大数据写入");

        //xls文件最大支持65536行
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 5; cellNum++) {
                // 创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("开始写入");
        FileOutputStream out = new FileOutputStream(PATH+"bigdata07.xlsx");
        workbook.write(out);
        // 关闭流
        out.close();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }


    @Test
    public void testWrite07BigDataS() throws IOException {

        long begin = System.currentTimeMillis();
        // 创建工作簿
        Workbook workbook = new SXSSFWorkbook(1000);
        // 创建工作表
        Sheet sheet = workbook.createSheet("07版本的大数据写入(增强版)");

        //xls文件最大支持65536行
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //创建行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 5; cellNum++) {
                // 创建列
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("开始写入");
        FileOutputStream out = new FileOutputStream(PATH+"bigdata07(增强版).xlsx");
        workbook.write(out);
        // 关闭流
        out.close();
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        //记录结束时间
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);
    }
}
